package com.foreknow.elm.dao.impl;

import com.foreknow.elm.dao.DeliveryAddressDao;
import com.foreknow.elm.po.DeliveryAddress;
import com.foreknow.elm.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class DeliveryAddressDaoImpl implements DeliveryAddressDao {
    private Connection con;
    private PreparedStatement pst;
    private ResultSet rs;

    @Override
    public List<DeliveryAddress> listDeliveryAddressByUserId(String userId) throws Exception {
        List<DeliveryAddress> list = new ArrayList<>();
        String sql = "SELECT * FROM deliveryaddress WHERE userId = ? order by daId";

        try {
            //进行数据库连接
            con = DBUtil.getConnection();
            //传输数据库语句
            pst = con.prepareStatement(sql);
            pst.setString(1,userId);
            //执行查询
            rs = pst.executeQuery();
            //遍历结果集合
            while (rs.next()){
                DeliveryAddress deliveryAddress = new DeliveryAddress();
                deliveryAddress.setDaId(rs.getInt("daId"));
                deliveryAddress.setAddress(rs.getString("address"));
                deliveryAddress.setContactName(rs.getString("contactName"));
                deliveryAddress.setContactSex(rs.getInt("contactSex"));
                deliveryAddress.setContactTel(rs.getString("contactTel"));
                deliveryAddress.setAddress(rs.getString("address"));
                deliveryAddress.setUserId(rs.getString("userId"));
                list.add(deliveryAddress);
            }
        }finally {
            DBUtil.close(rs,pst);
        }
        return list;
    }

    @Override
    public DeliveryAddress getDeliveryAddressById(int daId) throws Exception {
        String sql ="SELECT * FROM deliveryaddress WHERE daId = ?";
        DeliveryAddress deliveryAddress = new DeliveryAddress();
        try {
            //进行数据库连接
            con = DBUtil.getConnection();
            //传输数据库语句
            pst = con.prepareStatement(sql);
            pst.setInt(1, daId);

            rs = pst.executeQuery();
            while (rs.next()) {
                deliveryAddress.setDaId(rs.getInt("daId"));
                deliveryAddress.setContactName(rs.getString("contactName"));
                deliveryAddress.setContactSex(rs.getInt("contactSex"));
                deliveryAddress.setContactTel(rs.getString("contactTel"));
                deliveryAddress.setAddress(rs.getString("address"));
                deliveryAddress.setUserId(rs.getString("userId"));
            }
        }finally {  //这里不能处理异常，也就是没有catch，只有finally
            //这里负责关闭PreparedStatement和ResultSet
            DBUtil.close(rs, pst);
        }
        return deliveryAddress;
    }

    @Override
    public int saveDeliveryAddress(String contactName, int contactSex, String contactTel, String address, String userId) throws Exception {
        int isRight = 0;
        String sql = "INSERT INTO deliveryaddress(contactName,contactSex,contactTel,address,userId) VALUES (?,?,?,?,?)";
        try {
            //进行数据库连接
            con = DBUtil.getConnection();
            //传输数据库语句
            pst = con.prepareStatement(sql);

            pst.setString(1, contactName);
            pst.setInt(2, contactSex);
            pst.setString(3, contactTel);
            pst.setString(4, address);
            pst.setString(5, userId);
            //执行更新操作
            isRight = pst.executeUpdate();
        }finally {
            DBUtil.close(rs,pst);
        }
        return isRight;
    }

    @Override
    public int updateDeliveryAddress(int daId, String contactName, int contactSex, String contactTel, String address, String userId) throws Exception {
        int isRight = 0;
        String sql ="UPDATE deliveryaddress SET contactName=?,contactSex=?,contactTel=?,address=?,userId=? WHERE daId=?";
        try {
            //进行数据库连接
            con = DBUtil.getConnection();
            //传输数据库语句
            pst = con.prepareStatement(sql);

            pst.setString(1, contactName);
            pst.setInt(2, contactSex);
            pst.setString(3, contactTel);
            pst.setString(4, address);
            pst.setString(5, userId);
            pst.setInt(6,daId);
            //执行更新操作
            isRight = pst.executeUpdate();
        }finally {
            DBUtil.close(rs,pst);
        }
        return isRight;
    }

    @Override
    public int removeDeliveryAddress(int daId) throws Exception {
        int isRight = 0;
        String sql ="DELETE FROM deliveryaddress WHERE daId=?";
        try {
            //进行数据库连接
            con = DBUtil.getConnection();
            //传输数据库语句
            pst = con.prepareStatement(sql);

            pst.setInt(1,daId);
            //执行更新操作
            isRight = pst.executeUpdate();
        }finally {
            DBUtil.close(rs,pst);
        }
        return isRight;
    }
}
